<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="com.uc.training.smadmin.ord.dao.OrderDao">

	<!-- 根据对象的不为null的值作为条件进行查找 -->
	<select id="getOrderByMemberVO" resultClass="com.uc.training.smadmin.ord.model.Order" parameterClass="com.uc.training.smadmin.ord.vo.OrdMemberVO">
		 SELECT
             s.id AS id,
             s.member_id AS memberId,
             s.order_num AS orderNum,
             s.order_price AS orderPrice,
             s.pay_price AS payPrice,
             s.is_delete AS isDelete,
             s.status AS status,
             s.receipt_name AS receiptName,
             s.receipt_tel AS receiptTel,
             s.receipt_address AS receiptAddress
		FROM
          t_ord_order s
        WHERE
		  s.`is_delete` = 0 AND
          s.member_id=#memberId#
		<isNotEmpty property="status" prepend="and">
			s.status = #status#
		</isNotEmpty>
        <isNotEmpty property="orderNum" prepend="and">
            s.order_num = #orderNum#
        </isNotEmpty>
		<isNotEmpty property="orderId" prepend="and">
			s.id = #orderId#
		</isNotEmpty>
		ORDER BY
		  s.modify_time DESC
        <isNotNull property="offset">
            LIMIT #offset#, #pageSize#
        </isNotNull>

	</select>


	<!-- 根据对象的不为null的值作为条件进行查找 -->
	<select id="getTelephoneByOrderId" resultClass="String" parameterClass="String">
		SELECT
          s.receipt_tel
		FROM
          t_ord_order s
        WHERE
          s.id=#id#
	</select>


	<sql id="where_query">

		 <isNotEmpty property="id" prepend="and">
			 s.id = #id#
		 </isNotEmpty>
		 <isNotEmpty property="memberId" prepend="and">
			 s.member_id = #memberId#
		 </isNotEmpty>
		 <isNotEmpty property="orderNum" prepend="and">
			 s.order_num = #orderNum#
		 </isNotEmpty>
		 <isNotEmpty property="orderPrice" prepend="and">
			 s.order_price = #orderPrice#
		 </isNotEmpty>
		 <isNotEmpty property="payPrice" prepend="and">
			 s.pay_price = #payPrice#
		 </isNotEmpty>
		 <isNotEmpty property="isDelete" prepend="and">
			 s.is_delete = #isDelete#
		 </isNotEmpty>
		 <isNotEmpty property="status" prepend="and">
			 s.status = #status#
		 </isNotEmpty>
		 <isNotEmpty property="receiptName" prepend="and">
			 s.receipt_name = #receiptName#
		 </isNotEmpty>
		 <isNotEmpty property="receiptTel" prepend="and">
			 s.receipt_tel = #receiptTel#
		 </isNotEmpty>
		 <isNotEmpty property="receiptAddress" prepend="and">
			 s.receipt_address = #receiptAddress#
		 </isNotEmpty>
		 <isNotEmpty property="createEmp" prepend="and">
			 s.create_emp = #createEmp#
		 </isNotEmpty>
		 <isNotEmpty property="createTime" prepend="and">
			 s.create_time = #createTime#
		 </isNotEmpty>
		 <isNotEmpty property="modifyEmp" prepend="and">
			 s.modify_emp = #modifyEmp#
		 </isNotEmpty>
		 <isNotEmpty property="modifyTime" prepend="and">
			 s.modify_time = #modifyTime#
		 </isNotEmpty>
	</sql>

	<!-- 列表查询 -->
	<select id="queryOrderList" resultClass="com.uc.training.smadmin.ord.model.Order">
		 SELECT
		 s.member_id AS memberId,
		 s.order_num AS orderNum,
		 s.order_price AS orderPrice,
		 s.pay_price AS payPrice,
		 s.is_delete AS isDelete,
		 s.status AS status,
		 s.receipt_name AS receiptName,
		 s.receipt_tel AS receiptTel,
		 s.receipt_address AS receiptAddress,
		 s.create_emp AS createEmp,
		 s.create_time AS createTime,
		 s.modify_emp AS modifyEmp,
		 s.modify_time AS modifyTime
		 FROM t_ord_order s
		 WHERE 1=1
		 <!--  <isEmpty property="queryType"> order by s.id DESC </isEmpty>  -->
		 <!--  <isNotEmpty property="rows" prepend=" "> limit #start#,#rows#</isNotEmpty>/>  -->
	</select>

	<!-- 查找指定会员订单总记录数 -->
	<select id="queryOrderCount" resultClass="java.lang.Integer" parameterClass="java.lang.Long">
		 SELECT count(1)
		 FROM t_ord_order s
		 WHERE s.member_id = #memberId# AND s.is_delete = 0
	</select>

	<!-- 查找dai评价商品的属性id List -->
	<select id="queryOrderGoodsPropertyId" resultClass="com.uc.training.smadmin.ord.vo.CommentVO" parameterClass="com.uc.training.smadmin.ord.vo.OrdGoodsVO">
		SELECT
		    s.id as id,
			s.goods_property_id as goodsPropertyId,
			s.comment_status as commentStatus
		FROM
			t_ord_order_goods s
		LEFT JOIN t_ord_order ord
		on
			s.order_id = ord.id
		where
		ord.member_id = #memberId#
		AND ord.`status` = 5
		AND ord.is_delete = 0
		and s.`comment_status` = #commentStatus#
		ORDER BY
			s.goods_property_id DESC
	</select>

	<select id="getOrderByOrdnum" resultClass="com.uc.training.smadmin.ord.vo.OrdOrderVo">
		SELECT
		s.status as status
		FROM t_ord_order s
		WHERE
		s.`is_delete` = 0
		and s.order_num = #orderNum#
	</select>
	<!--查询订单表的总记录数-->
	<select id="OrderCount" resultClass="java.lang.Integer" parameterClass="com.uc.training.smadmin.ord.vo.OrdOrderVo">
		 SELECT count(1)
		 FROM t_ord_order s
		 WHERE
		s.`is_delete` = 0
		<isNotEmpty property="orderNum" prepend="and">
			s.order_num = #orderNum#
		</isNotEmpty>
		<isNotEmpty property="status" prepend="and">
			s.status = #status#
		</isNotEmpty>
		<isNotEmpty property="memberId" prepend="and">
			s.member_id = #memberId#
		</isNotEmpty>
	</select>
	<!-- 插入 -->
	<insert id="insertOrder" parameterClass="com.uc.training.smadmin.ord.model.Order">
		 insert into t_ord_order
			 (member_id, order_num, order_price, pay_price, is_delete, status, receipt_name, receipt_tel, receipt_address, create_time)
		values (#memberId#,#orderNum#,#orderPrice#,#payPrice#,#isDelete#,#status#,#receiptName#,#receiptTel#,#receiptAddress#,NOW())
	<selectKey keyProperty="id">
		select LAST_INSERT_ID() AS id
	</selectKey>
	</insert>

	<!-- 通过主键更新 -->
	<update id="updateOrderById" parameterClass="com.uc.training.smadmin.ord.model.Order">
		 update t_ord_order set
		id = #id#, member_id = #memberId#, order_num = #orderNum#, order_price = #orderPrice#, pay_price = #payPrice#, is_delete = #isDelete#, status = #status#, receipt_name = #receiptName#, receipt_tel = #receiptTel#, receipt_address = #receiptAddress#, create_emp = #createEmp#, create_time = #createTime#, modify_emp = #modifyEmp#, modify_time = #modifyTime#
		where id=#id#
		</update>

	<!-- 通过订单编号更新 -->
	<update id="updateOrder" parameterClass="com.uc.training.smadmin.ord.vo.OrdOrderVo">
		 update t_ord_order s
		 set
		s.status = #status#
		<isNotEmpty property="modifyEmp" prepend=",">
			s.modify_emp = #modifyEmp#
		</isNotEmpty>
		where
			s.order_num = #orderNum#
		<isNotEmpty property="memberId" prepend="and">
			s.member_id = #memberId#
		</isNotEmpty>
		</update>

	<!--查询分页-->
	<select id="getOrderPage" parameterClass="com.uc.training.smadmin.ord.vo.OrdOrderVo" resultClass="com.uc.training.smadmin.ord.re.OrderRe">
		SELECT
			s.id,
			s.member_id as memberId,
			s.order_num as orderNum,
			s.order_price as orderPrice,
			s.pay_price as payPrice,
			s.is_delete as isDelete,
			s.status as status,
			s.receipt_name as receiptName,
			s.receipt_tel as receiptTel,
			s.receipt_address as receiptAddress,
			s.create_time AS createTime
		FROM  t_ord_order s
		WHERE
		s.is_delete = 0
		<isNotEmpty property="orderNum" prepend="and">
			s.order_num = #orderNum#
		</isNotEmpty>
		<isNotEmpty property="status" prepend="and">
			s.status = #status#
		</isNotEmpty>
        ORDER BY
        s.create_time
        DESC
	</select>

	<update id="logicDelOrder" parameterClass="list">
		<!--BEGIN
		<iterate conjunction="">-->
			UPDATE t_ord_order s
			SET
			s.is_delete = 1
			WHERE
			s.id IN
		   <iterate open="(" close=")"  conjunction=",">
			   #list[]#
		   </iterate>

		<!--</iterate>-->

	</update>

	<update id="memberDelOrder" parameterClass="com.uc.training.smadmin.ord.vo.OrdOrderVo">
		UPDATE t_ord_order s
		SET
		s.is_delete = 1
		WHERE
		s.id =#orderId# AND
		s.member_id=#memberId#
	</update>

	<select id="getOrderSaleData" resultClass="com.uc.training.smadmin.sys.re.OrderSaleRE">
		SELECT
		    IFNULL(sum(case month(create_time) when '1' then pay_price else 0 end), 0) as january,
			IFNULL(sum(case month(create_time) when '2' then pay_price else 0 end), 0) as february,
			IFNULL(sum(case month(create_time) when '3' then pay_price else 0 end), 0) as march,
			IFNULL(sum(case month(create_time) when '4' then pay_price else 0 end), 0) as april,
			IFNULL(sum(case month(create_time) when '5' then pay_price else 0 end), 0) as may,
			IFNULL(sum(case month(create_time) when '6' then pay_price else 0 end), 0) as june,
			IFNULL(sum(case month(create_time) when '7' then pay_price else 0 end), 0) as july,
			IFNULL(sum(case month(create_time) when '8' then pay_price else 0 end), 0) as aguest,
			IFNULL(sum(case month(create_time) when '9' then pay_price else 0 end), 0) as september,
			IFNULL(sum(case month(create_time) when '10' then pay_price else 0 end), 0) as october,
			IFNULL(sum(case month(create_time) when '11' then pay_price else 0 end), 0) as november,
			IFNULL(sum(case month(create_time) when '12' then pay_price else 0 end), 0) as december
		FROM
			t_ord_order
		WHERE
			year(create_time) = YEAR(NOW())
			AND status > 2;
	</select>
</sqlMap>

